package com.aaa.sbms.dao;

/**
 * @FileName:TrainDao
 * @Description:
 * @Author :99728
 * @Date: 2020/6/16 21:23
 * @Version:1.0.0
 */

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 计划与实施
 */
public interface TrainDao {

    /**
     * 带参分页查询计划与实施
     * @param map
     * @return
     */
    @Select("<script>select trainid,u.userid,u.username,tr.trainername,co.coursename,date_format(starttime,'%Y-%m-%d') starttime,date_format(endtime,'%Y-%m-%d') endtime,gradetype  \n" +
            "from train join trainer tr on train.trainerid=tr.trainerid \n" +
            "join course co on co.courseid=train.courseid \n" +
            "join users u on u.userid=train.userid \n" +
            "<where>" +
            "<if test=\"coursename!=null\"> and co.coursename like concat('%',#{coursename},'%')</if>" +
            " <if test=\"userid!=null and userid !=''\"> and u.userid =#{userid} </if>"+
            "</where> order by trainid"+
            " limit #{begin},#{pageSize}</script>")
    List<Map> getPage(Map map);

    /**
     * 带参总条数计划与实施
     * @param map
     * @return
     */
    @Select("<script>select count(*)\n" +
            "from train join trainer tr on train.trainerid=tr.trainerid \n" +
            "join course co on co.courseid=train.courseid \n" +
            "join users u on u.userid=train.userid \n" +
            "<where>" +
            "<if test=\"coursename!=null\"> and co.coursename like concat('%',#{coursename},'%')</if>" +
            " <if test=\"userid!=null and userid !=''\"> and u.userid =#{userid} </if>"+
            "</where></script>")
    int getPageCount(Map map);


    /**
     * 下拉 课程名称查询
     * @param map
     * @return
     */
    @Select("select courseid,coursename from course")
    List<Map> getCourseName(Map map);

    /**
     * 下拉 根据对用的课程id获取培训师
     * @param map
     * @return
     */
    @Select("select trainerid,trainername from trainer \n" +
            "join course on course.coursename=trainer.coursename\n" +
            "where course.courseid = #{courseid}")
    List<Map> getTrainerName(Map map);

    /**
     * 下拉 根据课程id获取培训人
     * @param map
     * @return
     */
    @Select("select userid,username from users")
    List<Map> getUserName(Map map);


    /**
     * 添加
     * @param map
     * @return
     */
    @Insert("insert into train(userid,trainerid,starttime,endtime,courseid) " +
            "values(#{userid},#{trainerid},#{starttime},#{endtime},#{courseid})")
    int add(Map map);

    /**
     * 删除该ID关联的信息
     * @param trainid
     * @return
     */
    @Delete("delete from train  where trainid=#{trainid}")
    int delete(Integer trainid);
}
